{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 读/写不同数据源的数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读/写文本文件"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "正常读取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_records</th>\n",
       "      <th>value_actual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020/7/31 22:01</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020/7/23 6:12</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020/7/13 9:30</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020/6/30 14:26</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020/6/23 20:21</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013/12/8 3:10</td>\n",
       "      <td>1</td>\n",
       "      <td>6.205390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013/11/21 1:50</td>\n",
       "      <td>1</td>\n",
       "      <td>5.198931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013/10/12 16:06</td>\n",
       "      <td>1</td>\n",
       "      <td>6.339678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3006</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013/10/9 17:27</td>\n",
       "      <td>1</td>\n",
       "      <td>5.386197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013/9/24 15:31</td>\n",
       "      <td>1</td>\n",
       "      <td>6.795946</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3008 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            format            metric              date  number_of_records  \\\n",
       "0               CD             Units   2020/7/31 22:01                  1   \n",
       "1               CD             Units    2020/7/23 6:12                  1   \n",
       "2               CD             Units    2020/7/13 9:30                  1   \n",
       "3               CD             Units   2020/6/30 14:26                  1   \n",
       "4               CD             Units   2020/6/23 20:21                  1   \n",
       "...            ...               ...               ...                ...   \n",
       "3003  Vinyl Single  Value (Adjusted)    2013/12/8 3:10                  1   \n",
       "3004  Vinyl Single  Value (Adjusted)   2013/11/21 1:50                  1   \n",
       "3005  Vinyl Single  Value (Adjusted)  2013/10/12 16:06                  1   \n",
       "3006  Vinyl Single  Value (Adjusted)   2013/10/9 17:27                  1   \n",
       "3007  Vinyl Single  Value (Adjusted)   2013/9/24 15:31                  1   \n",
       "\n",
       "      value_actual  \n",
       "0              NaN  \n",
       "1              NaN  \n",
       "2              NaN  \n",
       "3              NaN  \n",
       "4              NaN  \n",
       "...            ...  \n",
       "3003      6.205390  \n",
       "3004      5.198931  \n",
       "3005      6.339678  \n",
       "3006      5.386197  \n",
       "3007      6.795946  \n",
       "\n",
       "[3008 rows x 5 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "musicdata = pd.read_table(\"data/musicdata.csv\", sep=',', encoding='gbk')\n",
    "musicdata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3008, 5)"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "不指定间隔"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3008, 1)"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata1 = pd.read_table(\"data/musicdata.csv\", encoding='gbk')\n",
    "musicdata1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "指定错误间隔"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format,metric,date,number_of_records,value_actual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CD,Units,2020/7/31 22:01,1,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CD,Units,2020/7/23 6:12,1,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CD,Units,2020/7/13 9:30,1,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CD,Units,2020/6/30 14:26,1,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CD,Units,2020/6/23 20:21,1,</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>Vinyl Single,Value (Adjusted),2013/12/8 3:10,1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>Vinyl Single,Value (Adjusted),2013/11/21 1:50,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>Vinyl Single,Value (Adjusted),2013/10/12 16:06...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3006</th>\n",
       "      <td>Vinyl Single,Value (Adjusted),2013/10/9 17:27,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>Vinyl Single,Value (Adjusted),2013/9/24 15:31,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3008 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      format,metric,date,number_of_records,value_actual\n",
       "0                           CD,Units,2020/7/31 22:01,1,\n",
       "1                            CD,Units,2020/7/23 6:12,1,\n",
       "2                            CD,Units,2020/7/13 9:30,1,\n",
       "3                           CD,Units,2020/6/30 14:26,1,\n",
       "4                           CD,Units,2020/6/23 20:21,1,\n",
       "...                                                 ...\n",
       "3003  Vinyl Single,Value (Adjusted),2013/12/8 3:10,1...\n",
       "3004  Vinyl Single,Value (Adjusted),2013/11/21 1:50,...\n",
       "3005  Vinyl Single,Value (Adjusted),2013/10/12 16:06...\n",
       "3006  Vinyl Single,Value (Adjusted),2013/10/9 17:27,...\n",
       "3007  Vinyl Single,Value (Adjusted),2013/9/24 15:31,...\n",
       "\n",
       "[3008 rows x 1 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata2 = pd.read_table(\"data/musicdata.csv\", sep=';')\n",
    "musicdata2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3008, 1)"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata2.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "指定header为None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>format</td>\n",
       "      <td>metric</td>\n",
       "      <td>date</td>\n",
       "      <td>number_of_records</td>\n",
       "      <td>value_actual</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020/7/31 22:01</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020/7/23 6:12</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020/7/13 9:30</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020/6/30 14:26</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013/12/8 3:10</td>\n",
       "      <td>1</td>\n",
       "      <td>6.205390253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013/11/21 1:50</td>\n",
       "      <td>1</td>\n",
       "      <td>5.198931395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3006</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013/10/12 16:06</td>\n",
       "      <td>1</td>\n",
       "      <td>6.33967756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013/10/9 17:27</td>\n",
       "      <td>1</td>\n",
       "      <td>5.386196747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3008</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013/9/24 15:31</td>\n",
       "      <td>1</td>\n",
       "      <td>6.795945687</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3009 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 0                 1                 2                  3  \\\n",
       "0           format            metric              date  number_of_records   \n",
       "1               CD             Units   2020/7/31 22:01                  1   \n",
       "2               CD             Units    2020/7/23 6:12                  1   \n",
       "3               CD             Units    2020/7/13 9:30                  1   \n",
       "4               CD             Units   2020/6/30 14:26                  1   \n",
       "...            ...               ...               ...                ...   \n",
       "3004  Vinyl Single  Value (Adjusted)    2013/12/8 3:10                  1   \n",
       "3005  Vinyl Single  Value (Adjusted)   2013/11/21 1:50                  1   \n",
       "3006  Vinyl Single  Value (Adjusted)  2013/10/12 16:06                  1   \n",
       "3007  Vinyl Single  Value (Adjusted)   2013/10/9 17:27                  1   \n",
       "3008  Vinyl Single  Value (Adjusted)   2013/9/24 15:31                  1   \n",
       "\n",
       "                 4  \n",
       "0     value_actual  \n",
       "1              NaN  \n",
       "2              NaN  \n",
       "3              NaN  \n",
       "4              NaN  \n",
       "...            ...  \n",
       "3004   6.205390253  \n",
       "3005   5.198931395  \n",
       "3006    6.33967756  \n",
       "3007   5.386196747  \n",
       "3008   6.795945687  \n",
       "\n",
       "[3009 rows x 5 columns]"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata3 = pd.read_csv(\"data/musicdata.csv\", sep=',', header=None, encoding='gbk')\n",
    "musicdata3\n",
    "# musicdata4 = pd.read_csv(\"data/musicdata.csv\", sep=',', encoding='utf-16')\n",
    "# musicdata4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 文本文件写入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['musicdata.csv', 'musicdata.xlsx']"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "os.listdir(\"tmp\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "存储可以选择间隔 索引 header"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['musicdata.csv', 'musicdata.xlsx']"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.to_csv(\"tmp/musicdata.csv\", sep=';', index=False)\n",
    "os.listdir(\"tmp\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 思考\n",
    "调整sep参数、index参数、head参数分别会有什么变化？"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 读写Excel文件"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "读取Excel\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_records</th>\n",
       "      <th>value_actual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-31 22:01:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-23 06:12:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-13 09:30:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-30 14:26:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-23 20:21:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-12-08 03:10:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.205390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-11-21 01:50:00</td>\n",
       "      <td>1</td>\n",
       "      <td>5.198931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-10-12 16:06:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.339678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3006</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-10-09 17:27:00</td>\n",
       "      <td>1</td>\n",
       "      <td>5.386197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-09-24 15:31:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.795946</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3008 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            format            metric                date  number_of_records  \\\n",
       "0               CD             Units 2020-07-31 22:01:00                  1   \n",
       "1               CD             Units 2020-07-23 06:12:00                  1   \n",
       "2               CD             Units 2020-07-13 09:30:00                  1   \n",
       "3               CD             Units 2020-06-30 14:26:00                  1   \n",
       "4               CD             Units 2020-06-23 20:21:00                  1   \n",
       "...            ...               ...                 ...                ...   \n",
       "3003  Vinyl Single  Value (Adjusted) 2013-12-08 03:10:00                  1   \n",
       "3004  Vinyl Single  Value (Adjusted) 2013-11-21 01:50:00                  1   \n",
       "3005  Vinyl Single  Value (Adjusted) 2013-10-12 16:06:00                  1   \n",
       "3006  Vinyl Single  Value (Adjusted) 2013-10-09 17:27:00                  1   \n",
       "3007  Vinyl Single  Value (Adjusted) 2013-09-24 15:31:00                  1   \n",
       "\n",
       "      value_actual  \n",
       "0              NaN  \n",
       "1              NaN  \n",
       "2              NaN  \n",
       "3              NaN  \n",
       "4              NaN  \n",
       "...            ...  \n",
       "3003      6.205390  \n",
       "3004      5.198931  \n",
       "3005      6.339678  \n",
       "3006      5.386197  \n",
       "3007      6.795946  \n",
       "\n",
       "[3008 rows x 5 columns]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## 读/写Excel文件\n",
    "musicdata = pd.read_excel(\"data/musicdata.xlsx\")\n",
    "musicdata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 思考\n",
    "报错应该如何处理？\n",
    "ImportError: Missing optional dependency 'openpyxl'.  Use pip or conda to install openpyxl."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: openpyxl in c:\\users\\ybli\\anaconda3\\lib\\site-packages (3.0.5)\n",
      "Requirement already satisfied: jdcal in c:\\users\\ybli\\anaconda3\\lib\\site-packages (from openpyxl) (1.4.1)\n",
      "Requirement already satisfied: et-xmlfile in c:\\users\\ybli\\anaconda3\\lib\\site-packages (from openpyxl) (1.0.1)\n"
     ]
    }
   ],
   "source": [
    "! pip install openpyxl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Excel文件写入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['musicdata.csv', 'musicdata.xlsx']"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.listdir(\"tmp\")\n",
    "musicdata.to_excel(\"tmp/musicdata.xlsx\")\n",
    "os.listdir(\"tmp\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 掌握DataFrame的常用操作"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 查看DataFrame常用属性\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "索引"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "索引： RangeIndex(start=0, stop=3008, step=1)\n"
     ]
    }
   ],
   "source": [
    "print(\"索引：\", musicdata.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "所有值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "所有值： [['CD' 'Units' Timestamp('2020-07-31 22:01:00') 1 nan]\n",
      " ['CD' 'Units' Timestamp('2020-07-23 06:12:00') 1 nan]\n",
      " ['CD' 'Units' Timestamp('2020-07-13 09:30:00') 1 nan]\n",
      " ...\n",
      " ['Vinyl Single' 'Value (Adjusted)' Timestamp('2013-10-12 16:06:00') 1\n",
      "  6.33967756]\n",
      " ['Vinyl Single' 'Value (Adjusted)' Timestamp('2013-10-09 17:27:00') 1\n",
      "  5.386196747]\n",
      " ['Vinyl Single' 'Value (Adjusted)' Timestamp('2013-09-24 15:31:00') 1\n",
      "  6.795945687]]\n"
     ]
    }
   ],
   "source": [
    "print(\"所有值：\", musicdata.values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "列名： Index(['format', 'metric', 'date', 'number_of_records', 'value_actual'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(\"列名：\", musicdata.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据类型\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据类型：\n",
      " format                       object\n",
      "metric                       object\n",
      "date                 datetime64[ns]\n",
      "number_of_records             int64\n",
      "value_actual                float64\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "print(\"数据类型：\\n\", musicdata.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "元素个数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "元素个数： 15040\n"
     ]
    }
   ],
   "source": [
    "print(\"元素个数：\", musicdata.size)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "维度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "维度： 2\n"
     ]
    }
   ],
   "source": [
    "print(\"维度：\", musicdata.ndim)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "形状"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "形状： (3008, 5)\n"
     ]
    }
   ],
   "source": [
    "print(\"形状：\", musicdata.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "转置属性 .T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>2998</th>\n",
       "      <th>2999</th>\n",
       "      <th>3000</th>\n",
       "      <th>3001</th>\n",
       "      <th>3002</th>\n",
       "      <th>3003</th>\n",
       "      <th>3004</th>\n",
       "      <th>3005</th>\n",
       "      <th>3006</th>\n",
       "      <th>3007</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>format</th>\n",
       "      <td>CD</td>\n",
       "      <td>CD</td>\n",
       "      <td>CD</td>\n",
       "      <td>CD</td>\n",
       "      <td>CD</td>\n",
       "      <td>CD</td>\n",
       "      <td>CD</td>\n",
       "      <td>CD</td>\n",
       "      <td>CD</td>\n",
       "      <td>CD</td>\n",
       "      <td>...</td>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Vinyl Single</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>metric</th>\n",
       "      <td>Units</td>\n",
       "      <td>Units</td>\n",
       "      <td>Units</td>\n",
       "      <td>Units</td>\n",
       "      <td>Units</td>\n",
       "      <td>Units</td>\n",
       "      <td>Units</td>\n",
       "      <td>Units</td>\n",
       "      <td>Units</td>\n",
       "      <td>Units</td>\n",
       "      <td>...</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <td>2020-07-31 22:01:00</td>\n",
       "      <td>2020-07-23 06:12:00</td>\n",
       "      <td>2020-07-13 09:30:00</td>\n",
       "      <td>2020-06-30 14:26:00</td>\n",
       "      <td>2020-06-23 20:21:00</td>\n",
       "      <td>2020-06-20 13:41:00</td>\n",
       "      <td>2020-05-24 07:03:00</td>\n",
       "      <td>2020-05-22 17:44:00</td>\n",
       "      <td>2020-05-14 09:30:00</td>\n",
       "      <td>2020-04-30 21:28:00</td>\n",
       "      <td>...</td>\n",
       "      <td>2014-02-13 06:47:00</td>\n",
       "      <td>2014-01-15 02:46:00</td>\n",
       "      <td>2014-01-12 16:00:00</td>\n",
       "      <td>2014-01-08 17:05:00</td>\n",
       "      <td>2013-12-23 08:30:00</td>\n",
       "      <td>2013-12-08 03:10:00</td>\n",
       "      <td>2013-11-21 01:50:00</td>\n",
       "      <td>2013-10-12 16:06:00</td>\n",
       "      <td>2013-10-09 17:27:00</td>\n",
       "      <td>2013-09-24 15:31:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>number_of_records</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>value_actual</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>2.696606</td>\n",
       "      <td>5.228183</td>\n",
       "      <td>5.281477</td>\n",
       "      <td>3.269213</td>\n",
       "      <td>5.938887</td>\n",
       "      <td>6.20539</td>\n",
       "      <td>5.198931</td>\n",
       "      <td>6.339678</td>\n",
       "      <td>5.386197</td>\n",
       "      <td>6.795946</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 3008 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  0                    1     \\\n",
       "format                              CD                   CD   \n",
       "metric                           Units                Units   \n",
       "date               2020-07-31 22:01:00  2020-07-23 06:12:00   \n",
       "number_of_records                    1                    1   \n",
       "value_actual                       NaN                  NaN   \n",
       "\n",
       "                                  2                    3     \\\n",
       "format                              CD                   CD   \n",
       "metric                           Units                Units   \n",
       "date               2020-07-13 09:30:00  2020-06-30 14:26:00   \n",
       "number_of_records                    1                    1   \n",
       "value_actual                       NaN                  NaN   \n",
       "\n",
       "                                  4                    5     \\\n",
       "format                              CD                   CD   \n",
       "metric                           Units                Units   \n",
       "date               2020-06-23 20:21:00  2020-06-20 13:41:00   \n",
       "number_of_records                    1                    1   \n",
       "value_actual                       NaN                  NaN   \n",
       "\n",
       "                                  6                    7     \\\n",
       "format                              CD                   CD   \n",
       "metric                           Units                Units   \n",
       "date               2020-05-24 07:03:00  2020-05-22 17:44:00   \n",
       "number_of_records                    1                    1   \n",
       "value_actual                       NaN                  NaN   \n",
       "\n",
       "                                  8                    9     ...  \\\n",
       "format                              CD                   CD  ...   \n",
       "metric                           Units                Units  ...   \n",
       "date               2020-05-14 09:30:00  2020-04-30 21:28:00  ...   \n",
       "number_of_records                    1                    1  ...   \n",
       "value_actual                       NaN                  NaN  ...   \n",
       "\n",
       "                                  2998                 2999  \\\n",
       "format                    Vinyl Single         Vinyl Single   \n",
       "metric                Value (Adjusted)     Value (Adjusted)   \n",
       "date               2014-02-13 06:47:00  2014-01-15 02:46:00   \n",
       "number_of_records                    1                    1   \n",
       "value_actual                  2.696606             5.228183   \n",
       "\n",
       "                                  3000                 3001  \\\n",
       "format                    Vinyl Single         Vinyl Single   \n",
       "metric                Value (Adjusted)     Value (Adjusted)   \n",
       "date               2014-01-12 16:00:00  2014-01-08 17:05:00   \n",
       "number_of_records                    1                    1   \n",
       "value_actual                  5.281477             3.269213   \n",
       "\n",
       "                                  3002                 3003  \\\n",
       "format                    Vinyl Single         Vinyl Single   \n",
       "metric                Value (Adjusted)     Value (Adjusted)   \n",
       "date               2013-12-23 08:30:00  2013-12-08 03:10:00   \n",
       "number_of_records                    1                    1   \n",
       "value_actual                  5.938887              6.20539   \n",
       "\n",
       "                                  3004                 3005  \\\n",
       "format                    Vinyl Single         Vinyl Single   \n",
       "metric                Value (Adjusted)     Value (Adjusted)   \n",
       "date               2013-11-21 01:50:00  2013-10-12 16:06:00   \n",
       "number_of_records                    1                    1   \n",
       "value_actual                  5.198931             6.339678   \n",
       "\n",
       "                                  3006                 3007  \n",
       "format                    Vinyl Single         Vinyl Single  \n",
       "metric                Value (Adjusted)     Value (Adjusted)  \n",
       "date               2013-10-09 17:27:00  2013-09-24 15:31:00  \n",
       "number_of_records                    1                    1  \n",
       "value_actual                  5.386197             6.795946  \n",
       "\n",
       "[5 rows x 3008 columns]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 查、改、增、删DataFrame数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 查看DataFrame数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用字典访问方式查询列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                 CD\n",
       "1                 CD\n",
       "2                 CD\n",
       "3                 CD\n",
       "4                 CD\n",
       "            ...     \n",
       "3003    Vinyl Single\n",
       "3004    Vinyl Single\n",
       "3005    Vinyl Single\n",
       "3006    Vinyl Single\n",
       "3007    Vinyl Single\n",
       "Name: format, Length: 3008, dtype: object"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata[\"format\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "访问属性查询列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       1\n",
       "1       1\n",
       "2       1\n",
       "3       1\n",
       "4       1\n",
       "       ..\n",
       "3003    1\n",
       "3004    1\n",
       "3005    1\n",
       "3006    1\n",
       "3007    1\n",
       "Name: number_of_records, Length: 3008, dtype: int64"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.number_of_records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "单列多行数据获取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Units\n",
       "1    Units\n",
       "2    Units\n",
       "3    Units\n",
       "4    Units\n",
       "Name: metric, dtype: object"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata[\"metric\"][:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "多列多行数据获取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>metric</th>\n",
       "      <th>format</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Units</td>\n",
       "      <td>CD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Units</td>\n",
       "      <td>CD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Units</td>\n",
       "      <td>CD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Units</td>\n",
       "      <td>CD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Units</td>\n",
       "      <td>CD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  metric format\n",
       "0  Units     CD\n",
       "1  Units     CD\n",
       "2  Units     CD\n",
       "3  Units     CD\n",
       "4  Units     CD"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata[[\"metric\", \"format\"]][:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "多行数据获取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_records</th>\n",
       "      <th>value_actual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-23 06:12:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-13 09:30:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-30 14:26:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-23 20:21:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-20 13:41:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  format metric                date  number_of_records  value_actual\n",
       "1     CD  Units 2020-07-23 06:12:00                  1           NaN\n",
       "2     CD  Units 2020-07-13 09:30:00                  1           NaN\n",
       "3     CD  Units 2020-06-30 14:26:00                  1           NaN\n",
       "4     CD  Units 2020-06-23 20:21:00                  1           NaN\n",
       "5     CD  Units 2020-06-20 13:41:00                  1           NaN"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata[:][1:6]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "查看前5行数据\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_records</th>\n",
       "      <th>value_actual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-31 22:01:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-23 06:12:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-13 09:30:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-30 14:26:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-23 20:21:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  format metric                date  number_of_records  value_actual\n",
       "0     CD  Units 2020-07-31 22:01:00                  1           NaN\n",
       "1     CD  Units 2020-07-23 06:12:00                  1           NaN\n",
       "2     CD  Units 2020-07-13 09:30:00                  1           NaN\n",
       "3     CD  Units 2020-06-30 14:26:00                  1           NaN\n",
       "4     CD  Units 2020-06-23 20:21:00                  1           NaN"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "查看后五行数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_records</th>\n",
       "      <th>value_actual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-12-08 03:10:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.205390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-11-21 01:50:00</td>\n",
       "      <td>1</td>\n",
       "      <td>5.198931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-10-12 16:06:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.339678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3006</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-10-09 17:27:00</td>\n",
       "      <td>1</td>\n",
       "      <td>5.386197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-09-24 15:31:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.795946</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            format            metric                date  number_of_records  \\\n",
       "3003  Vinyl Single  Value (Adjusted) 2013-12-08 03:10:00                  1   \n",
       "3004  Vinyl Single  Value (Adjusted) 2013-11-21 01:50:00                  1   \n",
       "3005  Vinyl Single  Value (Adjusted) 2013-10-12 16:06:00                  1   \n",
       "3006  Vinyl Single  Value (Adjusted) 2013-10-09 17:27:00                  1   \n",
       "3007  Vinyl Single  Value (Adjusted) 2013-09-24 15:31:00                  1   \n",
       "\n",
       "      value_actual  \n",
       "3003      6.205390  \n",
       "3004      5.198931  \n",
       "3005      6.339678  \n",
       "3006      5.386197  \n",
       "3007      6.795946  "
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用.loc[]进行单列切片"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                 CD\n",
       "1                 CD\n",
       "2                 CD\n",
       "3                 CD\n",
       "4                 CD\n",
       "            ...     \n",
       "3003    Vinyl Single\n",
       "3004    Vinyl Single\n",
       "3005    Vinyl Single\n",
       "3006    Vinyl Single\n",
       "3007    Vinyl Single\n",
       "Name: format, Length: 3008, dtype: object"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[:,\"format\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用iloc[]进行单列切片"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       1\n",
       "1       1\n",
       "2       1\n",
       "3       1\n",
       "4       1\n",
       "       ..\n",
       "3003    1\n",
       "3004    1\n",
       "3005    1\n",
       "3006    1\n",
       "3007    1\n",
       "Name: number_of_records, Length: 3008, dtype: int64"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.iloc[:, 3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "loc方法任意切片"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "format       CD\n",
       "metric    Units\n",
       "Name: 3, dtype: object"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[3, [\"format\", \"metric\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  format metric\n",
       "2     CD  Units\n",
       "3     CD  Units\n",
       "4     CD  Units\n",
       "5     CD  Units\n",
       "6     CD  Units"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[2:6, [\"format\", \"metric\"]]    # 闭区间"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "iloc方法任意切片"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "metric               Units\n",
       "number_of_records        1\n",
       "Name: 3, dtype: object"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.iloc[3, [1,3]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>metric</th>\n",
       "      <th>number_of_records</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Units</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Units</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Units</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Units</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Units</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  metric  number_of_records\n",
       "2  Units                  1\n",
       "3  Units                  1\n",
       "4  Units                  1\n",
       "5  Units                  1\n",
       "6  Units                  1"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.iloc[2:7, [1,3]]      # 前闭后开区间"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用条件进行切片"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CD</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>841</th>\n",
       "      <td>Paid Subscriptions</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>842</th>\n",
       "      <td>Paid Subscriptions</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>843</th>\n",
       "      <td>Paid Subscriptions</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>844</th>\n",
       "      <td>Paid Subscriptions</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>845</th>\n",
       "      <td>Paid Subscriptions</td>\n",
       "      <td>Units</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>846 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 format metric\n",
       "0                    CD  Units\n",
       "1                    CD  Units\n",
       "2                    CD  Units\n",
       "3                    CD  Units\n",
       "4                    CD  Units\n",
       "..                  ...    ...\n",
       "841  Paid Subscriptions  Units\n",
       "842  Paid Subscriptions  Units\n",
       "843  Paid Subscriptions  Units\n",
       "844  Paid Subscriptions  Units\n",
       "845  Paid Subscriptions  Units\n",
       "\n",
       "[846 rows x 2 columns]"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[musicdata[\"metric\"] == \"Units\", [\"format\", \"metric\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "# musicdata.iloc[musicdata[\"metric\"] == \"Units\", [1, 4]]      # 注意：iloc[]中不能使用条件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>metric</th>\n",
       "      <th>value_actual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Units</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Units</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Units</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Units</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Units</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>841</th>\n",
       "      <td>Units</td>\n",
       "      <td>10.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>842</th>\n",
       "      <td>Units</td>\n",
       "      <td>22.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>843</th>\n",
       "      <td>Units</td>\n",
       "      <td>35.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>844</th>\n",
       "      <td>Units</td>\n",
       "      <td>50.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>845</th>\n",
       "      <td>Units</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>846 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    metric  value_actual\n",
       "0    Units           NaN\n",
       "1    Units           NaN\n",
       "2    Units           NaN\n",
       "3    Units           NaN\n",
       "4    Units           NaN\n",
       "..     ...           ...\n",
       "841  Units          10.8\n",
       "842  Units          22.7\n",
       "843  Units          35.3\n",
       "844  Units          50.2\n",
       "845  Units           NaN\n",
       "\n",
       "[846 rows x 2 columns]"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.iloc[(musicdata[\"metric\"] == \"Units\").values, [1, 4]] # iloc[]中使用条件,传入array类型"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 思考\n",
    "\n",
    ".loc和iloc的区别是什么？\n",
    "\n",
    "loc[]是按标签索引，iloc[]是按位置索引\n",
    "\n",
    "建议多用loc[]，因为标签索引更直观，容易理解"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 更改DataFrame数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "更改数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       CD\n",
       "1       CD\n",
       "2       CD\n",
       "3       CD\n",
       "4       CD\n",
       "        ..\n",
       "2016    CD\n",
       "2017    CD\n",
       "2018    CD\n",
       "2019    CD\n",
       "2020    CD\n",
       "Name: format, Length: 141, dtype: object"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[(musicdata[\"format\"] == \"CD\"), \"format\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "musicdata.loc[(musicdata[\"format\"] == \"CD\"), \"format\"] = 1  # 会直接修改\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       1\n",
       "1       1\n",
       "2       1\n",
       "3       1\n",
       "4       1\n",
       "       ..\n",
       "2016    1\n",
       "2017    1\n",
       "2018    1\n",
       "2019    1\n",
       "2020    1\n",
       "Name: format, Length: 141, dtype: object"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[(musicdata[\"format\"] == 1), \"format\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 为DataFrame新增数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "新增非定值数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_records</th>\n",
       "      <th>value_actual</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-31 22:01:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-23 06:12:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-13 09:30:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-30 14:26:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-23 20:21:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  format metric                date  number_of_records  value_actual  month\n",
       "0      1  Units 2020-07-31 22:01:00                  1           NaN      7\n",
       "1      1  Units 2020-07-23 06:12:00                  1           NaN      7\n",
       "2      1  Units 2020-07-13 09:30:00                  1           NaN      7\n",
       "3      1  Units 2020-06-30 14:26:00                  1           NaN      6\n",
       "4      1  Units 2020-06-23 20:21:00                  1           NaN      6"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates = pd.to_datetime(musicdata[\"date\"])\n",
    "musicdata[\"month\"] = dates.map(lambda x : x.month)  # 添加变值\n",
    "musicdata.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "新增定值数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_records</th>\n",
       "      <th>value_actual</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-31 22:01:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-23 06:12:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-13 09:30:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-30 14:26:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-23 20:21:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  format metric                date  number_of_records  value_actual  month  \\\n",
       "0      1  Units 2020-07-31 22:01:00                  1           NaN      7   \n",
       "1      1  Units 2020-07-23 06:12:00                  1           NaN      7   \n",
       "2      1  Units 2020-07-13 09:30:00                  1           NaN      7   \n",
       "3      1  Units 2020-06-30 14:26:00                  1           NaN      6   \n",
       "4      1  Units 2020-06-23 20:21:00                  1           NaN      6   \n",
       "\n",
       "   day  \n",
       "0   15  \n",
       "1   15  \n",
       "2   15  \n",
       "3   15  \n",
       "4   15  "
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata[\"day\"] = 15\n",
    "musicdata.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 删除某列或某行"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "删除某列数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_records</th>\n",
       "      <th>value_actual</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-31 22:01:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-23 06:12:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-13 09:30:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-30 14:26:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-23 20:21:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-12-08 03:10:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.205390</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-11-21 01:50:00</td>\n",
       "      <td>1</td>\n",
       "      <td>5.198931</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-10-12 16:06:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.339678</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3006</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-10-09 17:27:00</td>\n",
       "      <td>1</td>\n",
       "      <td>5.386197</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-09-24 15:31:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.795946</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3008 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            format            metric                date  number_of_records  \\\n",
       "0                1             Units 2020-07-31 22:01:00                  1   \n",
       "1                1             Units 2020-07-23 06:12:00                  1   \n",
       "2                1             Units 2020-07-13 09:30:00                  1   \n",
       "3                1             Units 2020-06-30 14:26:00                  1   \n",
       "4                1             Units 2020-06-23 20:21:00                  1   \n",
       "...            ...               ...                 ...                ...   \n",
       "3003  Vinyl Single  Value (Adjusted) 2013-12-08 03:10:00                  1   \n",
       "3004  Vinyl Single  Value (Adjusted) 2013-11-21 01:50:00                  1   \n",
       "3005  Vinyl Single  Value (Adjusted) 2013-10-12 16:06:00                  1   \n",
       "3006  Vinyl Single  Value (Adjusted) 2013-10-09 17:27:00                  1   \n",
       "3007  Vinyl Single  Value (Adjusted) 2013-09-24 15:31:00                  1   \n",
       "\n",
       "      value_actual  month  \n",
       "0              NaN      7  \n",
       "1              NaN      7  \n",
       "2              NaN      7  \n",
       "3              NaN      6  \n",
       "4              NaN      6  \n",
       "...            ...    ...  \n",
       "3003      6.205390     12  \n",
       "3004      5.198931     11  \n",
       "3005      6.339678     10  \n",
       "3006      5.386197     10  \n",
       "3007      6.795946      9  \n",
       "\n",
       "[3008 rows x 6 columns]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.drop(labels=\"day\", axis=1, inplace=True)\n",
    "musicdata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "删除某几行数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>format</th>\n",
       "      <th>metric</th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_records</th>\n",
       "      <th>value_actual</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-07-31 22:01:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-23 20:21:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-06-20 13:41:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-05-24 07:03:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>Units</td>\n",
       "      <td>2020-05-22 17:44:00</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-12-08 03:10:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.205390</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-11-21 01:50:00</td>\n",
       "      <td>1</td>\n",
       "      <td>5.198931</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-10-12 16:06:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.339678</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3006</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-10-09 17:27:00</td>\n",
       "      <td>1</td>\n",
       "      <td>5.386197</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>Vinyl Single</td>\n",
       "      <td>Value (Adjusted)</td>\n",
       "      <td>2013-09-24 15:31:00</td>\n",
       "      <td>1</td>\n",
       "      <td>6.795946</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3005 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            format            metric                date  number_of_records  \\\n",
       "0                1             Units 2020-07-31 22:01:00                  1   \n",
       "4                1             Units 2020-06-23 20:21:00                  1   \n",
       "5                1             Units 2020-06-20 13:41:00                  1   \n",
       "6                1             Units 2020-05-24 07:03:00                  1   \n",
       "7                1             Units 2020-05-22 17:44:00                  1   \n",
       "...            ...               ...                 ...                ...   \n",
       "3003  Vinyl Single  Value (Adjusted) 2013-12-08 03:10:00                  1   \n",
       "3004  Vinyl Single  Value (Adjusted) 2013-11-21 01:50:00                  1   \n",
       "3005  Vinyl Single  Value (Adjusted) 2013-10-12 16:06:00                  1   \n",
       "3006  Vinyl Single  Value (Adjusted) 2013-10-09 17:27:00                  1   \n",
       "3007  Vinyl Single  Value (Adjusted) 2013-09-24 15:31:00                  1   \n",
       "\n",
       "      value_actual  month  \n",
       "0              NaN      7  \n",
       "4              NaN      6  \n",
       "5              NaN      6  \n",
       "6              NaN      5  \n",
       "7              NaN      5  \n",
       "...            ...    ...  \n",
       "3003      6.205390     12  \n",
       "3004      5.198931     11  \n",
       "3005      6.339678     10  \n",
       "3006      5.386197     10  \n",
       "3007      6.795946      9  \n",
       "\n",
       "[3005 rows x 6 columns]"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.drop(range(1, 4), axis=0, inplace=True)\n",
    "musicdata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 统计DataFrame数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数值型特征统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用numpy计算均值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "np.mean(musicdata.loc[:, \"number_of_records\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用pandas计算均值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[:, \"number_of_records\"].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数值型统计性信息描述"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>number_of_records</th>\n",
       "      <th>value_actual</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>3005.0</td>\n",
       "      <td>1351.000000</td>\n",
       "      <td>3005.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.0</td>\n",
       "      <td>781.291237</td>\n",
       "      <td>6.489185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2246.837672</td>\n",
       "      <td>3.473837</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.0</td>\n",
       "      <td>-7.650944</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.700228</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>63.900000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.0</td>\n",
       "      <td>448.900000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.0</td>\n",
       "      <td>19667.327786</td>\n",
       "      <td>12.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       number_of_records  value_actual        month\n",
       "count             3005.0   1351.000000  3005.000000\n",
       "mean                 1.0    781.291237     6.489185\n",
       "std                  0.0   2246.837672     3.473837\n",
       "min                  1.0     -7.650944     1.000000\n",
       "25%                  1.0      3.700228     3.000000\n",
       "50%                  1.0     63.900000     6.000000\n",
       "75%                  1.0    448.900000    10.000000\n",
       "max                  1.0  19667.327786    12.000000"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    3005.0\n",
       "mean        1.0\n",
       "std         0.0\n",
       "min         1.0\n",
       "25%         1.0\n",
       "50%         1.0\n",
       "75%         1.0\n",
       "max         1.0\n",
       "Name: number_of_records, dtype: float64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[:, \"number_of_records\"].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 类别型特征统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对音乐销售形式进行频数统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Download Album                        141\n",
       "Ringtones & Ringbacks                 141\n",
       "Cassette                              141\n",
       "Cassette Single                       141\n",
       "LP/EP                                 141\n",
       "Vinyl Single                          141\n",
       "8 - Track                             141\n",
       "Other Tapes                           141\n",
       "Music Video (Physical)                141\n",
       "DVD Audio                             141\n",
       "SACD                                  141\n",
       "Download Single                       141\n",
       "CD Single                             141\n",
       "Kiosk                                 141\n",
       "Download Music Video                  141\n",
       "1                                     138\n",
       "Paid Subscriptions                     94\n",
       "Limited Tier Paid Subscription         94\n",
       "On-Demand Streaming (Ad-Supported)     94\n",
       "Other Ad-Supported Streaming           94\n",
       "Other Digital                          94\n",
       "Paid Subscription                      94\n",
       "SoundExchange Distributions            94\n",
       "Synchronization                        94\n",
       "Name: format, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[:, \"format\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Value               1081\n",
       "Value (Adjusted)    1081\n",
       "Units                843\n",
       "Name: metric, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[:, \"metric\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "类别型数据统计描述"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      3005\n",
       "unique        3\n",
       "top       Value\n",
       "freq       1081\n",
       "Name: metric, dtype: object"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[:, \"metric\"].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "练习\n",
    "查看value_actual列的描述信息,打印出平均值。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     1351.000000\n",
       "mean       781.291237\n",
       "std       2246.837672\n",
       "min         -7.650944\n",
       "25%          3.700228\n",
       "50%         63.900000\n",
       "75%        448.900000\n",
       "max      19667.327786\n",
       "Name: value_actual, dtype: float64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "value_actual = musicdata.loc[:, \"value_actual\"].describe()\n",
    "value_actual"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "781.2912371141006\n"
     ]
    }
   ],
   "source": [
    "print(value_actual.get(\"mean\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "练习\n",
    "\n",
    "查看format列有几种类型？哪一种类型的数据最少？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count               3005\n",
       "unique                24\n",
       "top       Download Album\n",
       "freq                 141\n",
       "Name: format, dtype: object"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.loc[:, \"format\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "最少的类型是: ['Paid Subscriptions', 'Limited Tier Paid Subscription', 'On-Demand Streaming (Ad-Supported)', 'Other Ad-Supported Streaming', 'Other Digital', 'Paid Subscription', 'SoundExchange Distributions', 'Synchronization']\n",
      "数量是： 94\n"
     ]
    }
   ],
   "source": [
    "data = musicdata.loc[:, \"format\"].value_counts()[-8:]\n",
    "print(\"最少的类型是:\", list(data.index))\n",
    "print(\"数量是：\", data.values[-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 转换与处理时间数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "查看date列的类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('<M8[ns]')"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata[\"date\"].dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "从date列中提取年份中的前五个"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-126-f30e6e2d4cd9>:1: FutureWarning: Treating datetime data as categorical rather than numeric in `.describe` is deprecated and will be removed in a future version of pandas. Specify `datetime_is_numeric=True` to silence this warning and adopt the future behavior now.\n",
      "  musicdata[\"date\"].describe()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "count                    3005\n",
       "unique                    719\n",
       "top       2020-07-31 22:01:00\n",
       "freq                        5\n",
       "first     1988-04-15 03:03:00\n",
       "last      2020-07-31 22:01:00\n",
       "Name: date, dtype: object"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata[\"date\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[2020, 2020, 2020, 2020, 2020]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "year = [date.year for date in musicdata[\"date\"]]\n",
    "year[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "从date列中提取月份中的前五个"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[7, 6, 6, 5, 5]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "month = []\n",
    "for date in musicdata[\"date\"]:\n",
    "    month.append(date.month)\n",
    "month[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "提取日期中的前5个"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    31\n",
       "4    23\n",
       "5    20\n",
       "6    24\n",
       "7    22\n",
       "Name: date, dtype: int64"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata[\"date\"].map(lambda x:x.day)[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "提取最后五条数据的日期"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3003    2013-12-08\n",
       "3004    2013-11-21\n",
       "3005    2013-10-12\n",
       "3006    2013-10-09\n",
       "3007    2013-09-24\n",
       "Name: date, dtype: object"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.iloc[-5:, 2].map(lambda x:x.date())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "时间类型常见的属性"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "属性名称 | 含义\n",
    "----|----\n",
    "year | 年\n",
    "month| 月\n",
    "day  | 日\n",
    "hour | 时\n",
    "minute| 分\n",
    "second | 秒"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "时间类型常见方法"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "方法名称 | 含义\n",
    "--------|----\n",
    "date    | 日期\n",
    "time    | 时间\n",
    "weekday | 星期"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 使用分组聚合进行组内计算"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 使用transform()方法聚合数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "使用transform()方法实现组内离差标准化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['format', 'metric', 'date', 'number_of_records', 'value_actual',\n",
       "       'month'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pandas.core.groupby.generic.DataFrameGroupBy object at 0x10f423bb0>"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata_group = musicdata[['format', 'value_actual']].groupby('format')\n",
    "musicdata_group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>value_actual</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2988</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2989</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2990</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2991</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2992</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2993</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2994</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2995</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2996</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2997</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2998</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3000</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3001</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3002</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3003</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3004</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3005</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3006</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3007</th>\n",
       "      <td>0.125409</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      value_actual\n",
       "2988      0.125409\n",
       "2989      0.125409\n",
       "2990      0.125409\n",
       "2991      0.125409\n",
       "2992      0.125409\n",
       "2993      0.125409\n",
       "2994      0.125409\n",
       "2995      0.125409\n",
       "2996      0.125409\n",
       "2997      0.125409\n",
       "2998      0.125409\n",
       "2999      0.125409\n",
       "3000      0.125409\n",
       "3001      0.125409\n",
       "3002      0.125409\n",
       "3003      0.125409\n",
       "3004      0.125409\n",
       "3005      0.125409\n",
       "3006      0.125409\n",
       "3007      0.125409"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "musicdata_group.transform(lambda x: (x.mean() - x.min()) / (x.max() -x.min())).tail(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
